﻿// Project: Salient
// http://salient.codeplex.com
// 
// Copyright 2010, Sky Sanders <sky at skysanders.net>
// Dual licensed under the MIT or GPL Version 2 licenses.
// http://salient.codeplex.com/license
// 
// Date: April 25 2010 

#region

using System;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Configuration.Provider;
using System.Globalization;
using System.Linq;
using System.Reflection;
using System.Security.Cryptography;
using System.Security.Permissions;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Configuration;
using System.Web.Management;
using System.Web.Security;
using Salient.Web.DataAccess;
using Salient.Web.SQLite;
using Salient.Web.SQLite.Model;
using Salient.Web.Util;

#endregion

namespace Salient.Web.Security
{
    /// <summary>
    /// TODO: update exceptions to use SR and don't swallow any
    /// </summary>
    [AspNetHostingPermission(SecurityAction.LinkDemand, Level = AspNetHostingPermissionLevel.Minimal)]
    [AspNetHostingPermission(SecurityAction.InheritanceDemand, Level = AspNetHostingPermissionLevel.Minimal)]
    public class SQLiteMembershipProvider : MembershipProvider
    {


        ///<summary>
        ///</summary>
        public string EntityConnectionString
        {
            get { return _entityConnectionString; }
        }
        #region Constants

        private const int PasswordSize = 14;

        #endregion

        #region Readonly

        private static readonly DateTime NullDate = new DateTime(1754, 1, 1).ToUniversalTime();

        #endregion

        #region Fields

        private string _applicationName;

        private bool _enablePasswordReset;

        private bool _enablePasswordRetrieval;

        private string _entityConnectionString;

        private int _maxInvalidPasswordAttempts;

        private int _minRequiredNonAlphanumericCharacters;

        private int _minRequiredPasswordLength;

        private int _passwordAttemptWindow;

        private MembershipPasswordFormat _passwordFormat;

        private string _passwordStrengthRegularExpression;

        private bool _requiresQuestionAndAnswer;

        private bool _requiresUniqueEmail;



        #endregion

        #region Properties

        /// <summary>
        /// The name of the application using the custom membership provider.
        /// </summary>
        /// <returns>
        /// The name of the application using the custom membership provider.
        /// </returns>
        /// <remarks>
        /// The ApplicationName is used by the Roles class to associate users and roles with different applications. This enables 
        /// multiple applications to use the same database to store user and role information without running into conflicts 
        /// between duplicate user names or role names. Multiple ASP.NET applications can use the same database by specifying the 
        /// same value in the ApplicationName property. The ApplicationName property can be set programmatically, or it can be set 
        /// declaratively in the configuration file for the Web application using the applicationName attribute.
        ///
        /// If no ApplicationName property is specified, we recommend that the ApplicationVirtualPath property value be used.
        /// </remarks>
        public override string ApplicationName
        {
            get
            {
                if (String.IsNullOrEmpty(_applicationName))
                {
                    _applicationName = ProviderUtility.GetDefaultAppName();
                }
                return _applicationName;
            }
            set
            {
                if (value != null && value.Length > 256)
                {
                    throw new ProviderException(SR.GetString(SR.PersonalizationProviderApplicationNameExceedMaxLength, "256"));
                }
                _applicationName = value;
            }
        }

        /// <summary>
        /// Indicates whether the membership provider is configured to allow users to reset their passwords.
        /// </summary>
        /// <returns>
        /// true if the membership provider supports password reset; otherwise, false. The default is true.
        /// </returns>
        /// <remarks>
        /// EnablePasswordReset contains a Boolean value specified in the configuration file (Web.config) using the 
        /// enablePasswordReset attribute. The value indicates whether users can use the ResetPassword method to overwrite their 
        /// current password with a new, randomly generated password. This property is read-only.
        /// </remarks>
        public override bool EnablePasswordReset
        {
            get { return _enablePasswordReset; }
        }

        /// <summary>
        /// Indicates whether the membership provider is configured to allow users to retrieve their passwords.
        /// </summary>
        /// <returns>
        /// true if the membership provider is configured to support password retrieval; otherwise, false. The default is false.
        /// </returns>
        /// <remarks>
        /// The EnablePasswordRetrieval property contains a Boolean value specified in the configuration file (Web.config) using 
        /// the enablePasswordRetrieval attribute. The value indicates whether users can use the GetPassword method to retrieve 
        /// their current password from the data source. This property is read-only.
        /// 
        /// If your custom membership provider supports hashed passwords, your GetPassword method implementation should return 
        /// an exception if EnablePasswordRetrieval is set to true and the password format is set to Hashed. Hashed passwords 
        /// cannot be retrieved.
        /// </remarks>
        public override bool EnablePasswordRetrieval
        {
            get { return _enablePasswordRetrieval; }
        }

        /// <summary>
        /// Gets the number of invalid password or password-answer attempts allowed before the membership user is locked out.
        /// </summary>
        /// <returns>
        /// The number of invalid password or password-answer attempts allowed before the membership user is locked out.
        /// </returns>
        /// <remarks>
        /// The MaxInvalidPasswordAttempts property works in conjunction with the PasswordAttemptWindow property to guard 
        /// against an unwanted source guessing the password or password answer of a membership user through repeated attempts. 
        /// If the number of invalid passwords or password questions supplied for a membership user is greater than or equal to 
        /// the MaxInvalidPasswordAttempts property value within the number of minutes identified by the PasswordAttemptWindow, 
        /// then the membership user is locked out by setting the IsLockedOut property to true until the user is unlocked by a 
        /// call to the UnlockUser method. If a valid password or password answer is supplied before the MaxInvalidPasswordAttempts 
        /// value is reached, the counter that tracks the number of invalid attempts is reset to zero.
        /// 
        /// Invalid password and password-answer attempts accumulate independently of one another. For example, if the 
        /// MaxInvalidPasswordAttempts property is set to 5, and three invalid password attempts are made followed by two invalid 
        /// password-answer attempts, two more invalid password attempts (or three more invalid password-answer attempts) must be 
        /// made within the PasswordAttemptWindow for the membership user to be locked out.
        /// 
        /// If the RequiresQuestionAndAnswer property is set to false, invalid password-answer attempts are not tracked.
        /// 
        /// Invalid password and password-answer attempts are tracked in the ValidateUser, ChangePassword, 
        /// ChangePasswordQuestionAndAnswer, GetPassword, and ResetPassword methods.
        /// </remarks>
        public override int MaxInvalidPasswordAttempts
        {
            get { return _maxInvalidPasswordAttempts; }
        }

        /// <summary>
        /// Gets the minimum number of special characters that must be present in a valid password.
        /// </summary>
        /// <returns>
        /// The minimum number of special characters that must be present in a valid password.
        /// </returns>
        /// <remarks>
        /// The MinRequiredNonAlphanumericCharacters property returns the minimum number of special, non-alphabetic characters 
        /// that must be entered to create a valid password for the MembershipProvider object.
        /// 
        /// The MinRequiredNonAlphanumericCharacters property value is set in the application configuration using the 
        /// minRequiredNonalphanumericCharacters attribute of the membership Element (ASP.NET Settings Schema) configuration 
        /// element.
        /// 
        /// A non-alphanumeric character is a character for which the IsLetterOrDigit method returns false.
        /// </remarks>
        public override int MinRequiredNonAlphanumericCharacters
        {
            get { return _minRequiredNonAlphanumericCharacters; }
        }

        /// <summary>
        /// Gets the minimum length required for a password.
        /// </summary>
        /// <returns>
        /// The minimum length required for a password. 
        /// </returns>
        /// <remarks>
        /// The MinRequiredPasswordLength property gets the minimum number of characters that must be entered to create a valid 
        /// password for the MembershipProvider object.
        /// 
        /// The MinRequiredPasswordLength property value is set in the application configuration using the 
        /// minRequiredPasswordLength attribute of the membership Element (ASP.NET Settings Schema) configuration element.
        /// </remarks>
        public override int MinRequiredPasswordLength
        {
            get { return _minRequiredPasswordLength; }
        }

        /// <summary>
        /// Gets the number of minutes in which a maximum number of invalid password or password-answer attempts are allowed 
        /// before the membership user is locked out.
        /// </summary>
        /// <returns>
        /// The number of minutes in which a maximum number of invalid password or password-answer attempts are allowed before 
        /// the membership user is locked out.
        /// </returns>
        /// <remarks>
        /// The PasswordAttemptWindow property works in conjunction with the MaxInvalidPasswordAttempts property to guard against 
        /// an unwanted source guessing the password or password answer of a membership user through repeated attempts. 
        /// If the number of invalid passwords or password questions supplied for a membership user exceeds the 
        /// MaxInvalidPasswordAttempts within the number of minutes identified by the PasswordAttemptWindow, then the membership 
        /// user is locked out by setting the IsLockedOut property to true until the user is unlocked by a call to the UnlockUser 
        /// method. If a valid password or password answer is supplied before the MaxInvalidPasswordAttempts is reached, the counter 
        /// that tracks the number of invalid attempts is reset to zero.
        /// 
        /// Invalid password and password-answer attempts accumulate independently of one another. For example, if the 
        /// MaxInvalidPasswordAttempts property is set to 5, and three invalid password attempts are made followed by two invalid 
        /// password-answer attempts, two more invalid password attempts (or three more invalid password-answer attempts) must be made 
        /// within the PasswordAttemptWindow value for the membership user to be locked out.
        /// 
        /// If the RequiresQuestionAndAnswer property is set to false, invalid password-answer attempts are not tracked.
        /// 
        /// Invalid password and password-answer attempts are tracked in the ValidateUser, ChangePassword, 
        /// ChangePasswordQuestionAndAnswer, GetPassword, and ResetPassword methods.
        /// </remarks>
        public override int PasswordAttemptWindow
        {
            get { return _passwordAttemptWindow; }
        }

        /// <summary>
        /// Gets a value indicating the format for storing passwords in the membership data store.
        /// </summary>
        /// <returns>
        /// One of the <see cref="T:System.Web.Security.MembershipPasswordFormat"/> values indicating the format for storing 
        /// passwords in the data store.
        /// </returns>
        /// <remarks>
        /// The PasswordFormat property indicates the format that passwords are stored in. Passwords can be stored in Clear, 
        /// Encrypted, and Hashed password formats. Clear passwords are stored in plain text, which improves the performance of 
        /// password storage and retrieval but is less secure, as passwords are easily read if your data source is compromised. 
        /// Encrypted passwords are encrypted when stored and can be decrypted for password comparison or password retrieval. 
        /// This requires additional processing for password storage and retrieval, but is more secure, as passwords are not 
        /// easily determined if the data source is compromised. Hashed passwords are hashed using a one-way hash algorithm 
        /// and a randomly generated salt value when stored in the database. When a password is validated, it is hashed with 
        /// the salt value in the database for verification. Hashed passwords cannot be retrieved.
        /// 
        /// You can use the EncryptPassword and DecryptPassword virtual methods of the MembershipProvider class to encrypt and 
        /// decrypt password values, or you can supply your own encryption code. If you use the EncryptPassword and DecryptPassword 
        /// virtual methods of the MembershipProvider class, Encrypted passwords are encrypted using the key information supplied 
        /// in the machineKey Element (ASP.NET Settings Schema) element in your configuration.
        /// </remarks>
        public override MembershipPasswordFormat PasswordFormat
        {
            get { return _passwordFormat; }
        }

        /// <summary>
        /// Gets the regular expression used to evaluate a password.
        /// </summary>
        /// <returns>
        /// A regular expression used to evaluate a password.
        /// </returns>
        /// <remarks>
        /// The PasswordStrengthRegularExpression property gets the regular expression used to evaluate password complexity for 
        /// the MembershipProvider object.
        /// 
        /// The PasswordStrengthRegularExpression property is set in the application configuration using the 
        /// passwordStrengthRegularExpression attribute of the membership Element (ASP.NET Settings Schema) configuration element.
        /// </remarks>
        public override string PasswordStrengthRegularExpression
        {
            get { return _passwordStrengthRegularExpression; }
        }

        /// <summary>
        /// Gets a value indicating whether the membership provider is configured to require the user to answer a password question 
        /// for password reset and retrieval.
        /// </summary>
        /// <returns>
        /// true if a password answer is required for password reset and retrieval; otherwise, false. The default is true.
        /// </returns>
        /// <remarks>
        /// A Boolean value specified in the configuration file (Web.config). The RequiresQuestionAndAnswer property indicates whether 
        /// users must supply a password answer in order to retrieve their password using the GetPassword method, or reset their password 
        /// using the ResetPassword method.
        /// 
        /// This property is read-only.
        /// </remarks>
        public override bool RequiresQuestionAndAnswer
        {
            get { return _requiresQuestionAndAnswer; }
        }

        /// <summary>
        /// Gets a value indicating whether the membership provider is configured to require a unique e-mail address for each user name.
        /// </summary>
        /// <returns>
        /// true if the membership provider requires a unique e-mail address; otherwise, false. The default is true.
        /// </returns>
        /// <remarks>
        /// The RequiresUniqueEmail property indicates whether users must supply a unique e-mail address value when creating a user. 
        /// If a user already exists in the data source for the current ApplicationName, then the CreateUser method returns null and a 
        /// status value of DuplicateEmail.
        /// </remarks>
        public override bool RequiresUniqueEmail
        {
            get { return _requiresUniqueEmail; }
        }

        #endregion
         
        #region Public Methods

        /// <summary>
        /// Processes a request to update the password for a membership user.
        /// </summary>
        /// <returns>
        /// true if the password was updated successfully; otherwise, false.
        /// </returns>
        /// <param name="username">The user to update the password for. </param>
        /// <param name="oldPassword">The current password for the specified user. </param>
        /// <param name="newPassword">The new password for the specified user. </param>
        /// <remarks>
        /// ChangePassword
        /// Takes, as input, a user name, a current password, and a new password, and updates the password in the data source if the 
        /// supplied user name and current password are valid.
        /// </remarks>
        public override bool ChangePassword(string username, string oldPassword, string newPassword)
        {
            ProviderUtility.CheckParameter(ref username, true, true, true, 256, "username");
            ProviderUtility.CheckParameter(ref oldPassword, true, true, false, 128, "oldPassword");
            ProviderUtility.CheckParameter(ref newPassword, true, true, false, 128, "newPassword");

            string salt;
            int passwordFormat;

            if (!CheckPassword(username, oldPassword, false, false, out salt, out passwordFormat))
            {
                return false;
            }

            if (newPassword.Length < MinRequiredPasswordLength)
            {
                throw new ArgumentException(SR.GetString(SR.PasswordTooShort, "newPassword",
                                                         MinRequiredPasswordLength.ToString(CultureInfo.InvariantCulture)));
            }

            int count = 0;

            for (int i = 0; i < newPassword.Length; i++)
            {
                if (!char.IsLetterOrDigit(newPassword, i))
                {
                    count++;
                }
            }

            if (count < MinRequiredNonAlphanumericCharacters)
            {
                throw new ArgumentException(SR.GetString(SR.PasswordNeedMoreNonAlphaNumericChars, "newPassword",
                                                         MinRequiredNonAlphanumericCharacters.ToString(
                                                             CultureInfo.InvariantCulture)));
            }

            if (PasswordStrengthRegularExpression.Length > 0)
            {
                if (!Regex.IsMatch(newPassword, PasswordStrengthRegularExpression))
                {
                    throw new ArgumentException(SR.GetString(SR.PasswordDoesNotMatchRegularExpression, "newPassword"));
                }
            }

            string pass = EncodePassword(newPassword, passwordFormat, salt);
            if (pass.Length > 128)
            {
                throw new ArgumentException(SR.GetString(SR.MembershipPasswordTooLong), "newPassword");
            }

            ValidatePasswordEventArgs e = new ValidatePasswordEventArgs(username, newPassword, false);
            OnValidatingPassword(e);

            if (e.Cancel)
            {
                if (e.FailureInformation != null)
                {
                    throw e.FailureInformation;
                }
                throw new ArgumentException(SR.GetString(SR.MembershipCustomPasswordValidationFailure),
                                            "newPassword");
            }


            using (var holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString))
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    Application app = GetOrCreateApplication(ctx);
                    using (var tx = holder.StoreConnection.BeginTransaction())
                    {
                        try
                        {
                            
                            AspUserMergedView userV =
                                ctx.AspUsersMergedView.FirstOrDefault(
                                    u => u.ApplicationId == app.ApplicationId && u.LoweredUserName == username.ToLower());

                            if (userV == null)
                            {
                                throw new ProviderException(GetExceptionText(MembershipErrorCode.MembershipUserNotFound));
                            }


                            AspMembershipUser muser = ctx.AspMembershipUsers.First(u => u.UserId == userV.UserId);
                            muser.Password = EncodePassword(newPassword, passwordFormat, salt);
                            muser.PasswordSalt = salt;
                            muser.PasswordFormat = passwordFormat;
                            muser.LastPasswordChangedDate = ProviderUtility.Now();
                            ctx.SaveChanges();
                            tx.Commit();
                        }
                        catch (Exception ex)
                        {
                            tx.Rollback();
                            this.LogError(ex);
                            throw new ProviderException(GetExceptionText(MembershipErrorCode.ProviderError));
                        }
                    }
                }
            }
            return true;


        }

        /// <summary>
        /// Processes a request to update the password question and answer for a membership user.
        /// </summary>
        /// <returns>
        /// true if the password question and answer are updated successfully; otherwise, false.
        /// </returns>
        /// <param name="username">The user to change the password question and answer for. </param>
        /// <param name="password">The password for the specified user. </param>
        /// <param name="newPasswordQuestion">The new password question for the specified user. </param>
        /// <param name="newPasswordAnswer">The new password answer for the specified user. </param>
        /// <remarks>
        /// Takes, as input, a user name, a password, a password question and a password answer, and updates the password 
        /// question and answer in the data source if the supplied user name and password are valid.
        /// 
        /// If the supplied user name and password are not valid, false is returned.
        /// </remarks>
        public override bool ChangePasswordQuestionAndAnswer(string username, string password,
                                                             string newPasswordQuestion, string newPasswordAnswer)
        {
            ProviderUtility.CheckParameter(ref username, true, true, true, 256, "username");
            ProviderUtility.CheckParameter(ref password, true, true, false, 128, "password");

            string salt;
            int passwordFormat;
            if (!CheckPassword(username, password, false, false, out salt, out passwordFormat))
                return false;
            ProviderUtility.CheckParameter(ref newPasswordQuestion, RequiresQuestionAndAnswer, RequiresQuestionAndAnswer,
                                      false, 256, "newPasswordQuestion");

            string encodedPasswordAnswer;

            if (newPasswordAnswer != null)
            {
                newPasswordAnswer = newPasswordAnswer.Trim();
            }

            ProviderUtility.CheckParameter(ref newPasswordAnswer, RequiresQuestionAndAnswer, RequiresQuestionAndAnswer, false,
                                      128, "newPasswordAnswer");

            if (!string.IsNullOrEmpty(newPasswordAnswer))
            {
                encodedPasswordAnswer = EncodePassword(newPasswordAnswer.ToLower(CultureInfo.InvariantCulture),
                                                       passwordFormat, salt);
            }
            else
            {
                encodedPasswordAnswer = newPasswordAnswer;
            }

            ProviderUtility.CheckParameter(ref encodedPasswordAnswer, RequiresQuestionAndAnswer, RequiresQuestionAndAnswer,
                                      false, 128, "newPasswordAnswer");

            using (
                var holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString)
                )
            {


                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    Application app = GetOrCreateApplication(ctx);
                    using (var tx = holder.StoreConnection.BeginTransaction())
                    {
                        try
                        {
                            AspUserMergedView userV =
                                ctx.AspUsersMergedView.FirstOrDefault(
                                    u => u.ApplicationId == app.ApplicationId && u.LoweredUserName == username.ToLower());
                            if (userV == null)
                            {
                                throw new ProviderException(GetExceptionText(MembershipErrorCode.MembershipUserNotFound));
                            }

                            AspMembershipUser muser = ctx.AspMembershipUsers.First(u => u.UserId == userV.UserId);
                            muser.PasswordQuestion = newPasswordQuestion;
                            muser.PasswordAnswer = encodedPasswordAnswer;


                            ctx.SaveChanges();
                            tx.Commit();
                            return true;
                        }
                        catch (Exception ex)
                        {
                            tx.Rollback();
                            this.LogError(ex);
                            throw new ProviderException(GetExceptionText(MembershipErrorCode.ProviderError));
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Adds a new membership user to the data source.
        /// </summary>
        /// <returns>
        /// A <see cref="T:System.Web.Security.MembershipUser"/> object populated with the information for the newly created user.
        /// </returns>
        /// <param name="username">The user name for the new user. </param>
        /// <param name="password">The password for the new user. </param>
        /// <param name="email">The e-mail address for the new user.</param>
        /// <param name="passwordQuestion">The password question for the new user.</param>
        /// <param name="passwordAnswer">The password answer for the new user</param>
        /// <param name="isApproved">Whether or not the new user is approved to be validated.</param>
        /// <param name="providerUserKey">The unique identifier from the membership data source for the user.</param>
        /// <param name="status">
        /// A <see cref="T:System.Web.Security.MembershipCreateStatus"/> enumeration value indicating whether the user was created 
        /// successfully.</param>
        /// <remarks>
        /// Takes, as input, the name of a new user, a password, and an e-mail address and inserts a new user for the application 
        /// into the data source. The CreateUser method returns a MembershipUser object populated with the information for the newly 
        /// created user. The CreateUser method also defines an out parameter (in Visual Basic, you can use ByRef) that returns a 
        /// MembershipCreateStatus value that indicates that the user was successfully created, or a reason that user creation failed.
        /// 
        /// The CreateUser method raises the ValidatingPassword event, if a MembershipValidatePasswordEventHandler event handler has 
        /// been specified, and continues or cancels the create-user action based on the results of the event. You can use the 
        /// OnValidatingPassword virtual method to execute the specified MembershipValidatePasswordEventHandler event handler.
        /// </remarks>
        public override MembershipUser CreateUser(string username, string password, string email,
                                                  string passwordQuestion, string passwordAnswer, bool isApproved,
                                                  object providerUserKey, out MembershipCreateStatus status)
        {
            if (!ProviderUtility.ValidateParameter(ref password, true, true, false, 128))
            {
                status = MembershipCreateStatus.InvalidPassword;
                return null;
            }

            string salt = GenerateSalt();
            string pass = EncodePassword(password, (int)_passwordFormat, salt);
            if (pass.Length > 128)
            {
                status = MembershipCreateStatus.InvalidPassword;
                return null;
            }

            string encodedPasswordAnswer;
            if (passwordAnswer != null)
            {
                passwordAnswer = passwordAnswer.Trim();
            }

            if (!string.IsNullOrEmpty(passwordAnswer))
            {
                if (passwordAnswer.Length > 128)
                {
                    status = MembershipCreateStatus.InvalidAnswer;
                    return null;
                }
                encodedPasswordAnswer = EncodePassword(passwordAnswer.ToLower(CultureInfo.InvariantCulture),
                                                       (int)_passwordFormat, salt);
            }
            else
                encodedPasswordAnswer = passwordAnswer;
            if (!ProviderUtility.ValidateParameter(ref encodedPasswordAnswer, RequiresQuestionAndAnswer, true, false, 128))
            {
                status = MembershipCreateStatus.InvalidAnswer;
                return null;
            }

            if (!ProviderUtility.ValidateParameter(ref username, true, true, true, 256))
            {
                status = MembershipCreateStatus.InvalidUserName;
                return null;
            }

            if (!ProviderUtility.ValidateParameter(ref email,
                                              RequiresUniqueEmail,
                                              RequiresUniqueEmail,
                                              false,
                                              256))
            {
                status = MembershipCreateStatus.InvalidEmail;
                return null;
            }

            if (!ProviderUtility.ValidateParameter(ref passwordQuestion, RequiresQuestionAndAnswer, true, false, 256))
            {
                status = MembershipCreateStatus.InvalidQuestion;
                return null;
            }

            if (providerUserKey != null)
            {
                if (!(providerUserKey is Guid))
                {
                    status = MembershipCreateStatus.InvalidProviderUserKey;
                    return null;
                }
            }

            if (password.Length < MinRequiredPasswordLength)
            {
                status = MembershipCreateStatus.InvalidPassword;
                return null;
            }

            int count = 0;

            for (int i = 0; i < password.Length; i++)
            {
                if (!char.IsLetterOrDigit(password, i))
                {
                    count++;
                }
            }

            if (count < MinRequiredNonAlphanumericCharacters)
            {
                status = MembershipCreateStatus.InvalidPassword;
                return null;
            }

            if (PasswordStrengthRegularExpression.Length > 0)
            {
                if (!Regex.IsMatch(password, PasswordStrengthRegularExpression))
                {
                    status = MembershipCreateStatus.InvalidPassword;
                    return null;
                }
            }

            ValidatePasswordEventArgs e = new ValidatePasswordEventArgs(username, password, true);
            OnValidatingPassword(e);

            if (e.Cancel)
            {
                status = MembershipCreateStatus.InvalidPassword;
                return null;
            }
            MembershipUser membershipUser;
            Guid? userId = providerUserKey as Guid?;
            bool newUserCreated = false;
            MembershipCreateStatus createStatus = MembershipCreateStatus.Success;
            DateTime createDate = ProviderUtility.Now();



            using (var holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString))
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    Application app = GetOrCreateApplication(ctx);
                    using (var tx = holder.StoreConnection.BeginTransaction())
                    {
                        try
                        {
                            AspUser user = ctx.AspUsers.FirstOrDefault(u => u.LoweredUserName == username.ToLower());
                            Guid uid = Guid.Empty;
                            // needed for work in closures
                            if (user == null)
                            {
                                if (userId.HasValue)
                                {
                                    uid = userId.Value;
                                    if (ctx.AspUsers.Any(u => u.UserId == uid))
                                    {
                                        createStatus = MembershipCreateStatus.DuplicateProviderUserKey;
                                        return null;
                                    }
                                }

                                user = new AspUser
                                    {
                                        Application = app,
                                        UserName = username,
                                        LoweredUserName = username.ToLower(),
                                        LastActivityDate = createDate,
                                        UserId = userId.GetValueOrDefault()
                                    };
                                ctx.AddToAspUsers(user);
                                ctx.SaveChanges();
                                newUserCreated = true;
                            }
                            else if (userId.HasValue && userId != user.UserId)
                            {
                                createStatus = MembershipCreateStatus.DuplicateUserName;
                                return null;
                            }

                            if (ctx.AspMembershipUsers.Any(u => u.UserId == user.UserId))
                            {
                                createStatus = MembershipCreateStatus.DuplicateUserName;
                                return null;
                            }
                            if (RequiresUniqueEmail)
                                if (ctx.AspMembershipUsers.Any(u => u.LoweredEmail == email.ToLower()))
                                {
                                    createStatus = MembershipCreateStatus.DuplicateEmail;
                                    return null;
                                }
                            if (!newUserCreated)
                                user.LastActivityDate = createDate;
                            AspMembershipUser muser = new AspMembershipUser
                                {
                                    UserId = uid,
                                    Application = app,
                                    AspUser = user,
                                    Password = pass,
                                    PasswordSalt = salt,
                                    Email = email,
                                    LoweredEmail = email.ToLower(),
                                    PasswordQuestion = passwordQuestion,
                                    PasswordAnswer = encodedPasswordAnswer,
                                    PasswordFormat = (int)PasswordFormat,
                                    IsApproved = isApproved,
                                    IsLockedOut = false,
                                    CreateDate = createDate,
                                    LastLoginDate = createDate,
                                    LastPasswordChangedDate = NullDate,
                                    LastLockoutDate = NullDate,
                                    FailedPasswordAttemptCount = 0,
                                    FailedPasswordAttemptWindowStart = NullDate,
                                    FailedPasswordAnswerAttemptCount = 0,
                                    FailedPasswordAnswerAttemptWindowStart = NullDate
                                };
                            ctx.AddToAspMembershipUsers(muser);
                            ctx.SaveChanges();
                            tx.Commit();
                            createStatus = MembershipCreateStatus.Success;
                            membershipUser = GetUser(user.UserId, true);
                        }
                        catch (Exception ex)
                        {
                            tx.Rollback();
                            this.LogError(ex);
                            throw new ProviderException(GetExceptionText(MembershipErrorCode.ProviderError));
                        }
                        finally
                        {
                            status = createStatus;
                        }
                    }
                }
            }

            return membershipUser;
        }

        /// <summary>
        /// Removes a user from the membership data source. 
        /// </summary>
        /// <returns>
        /// true if the user was successfully deleted; otherwise, false.
        /// </returns>
        /// <param name="username">The name of the user to delete.</param>
        /// <param name="deleteAllRelatedData">
        /// true to delete data related to the user from the database; false to leave data related to the user in the database.
        /// </param>
        /// <remarks>
        /// Takes, as input, the name of a user and deletes that user's information from the data source. If deleteAllRelatedData 
        /// is true, then all user data stored in the database for the Roles, Profile, and WebPart personalization is also deleted.
        /// </remarks>
        public override bool DeleteUser(string username, bool deleteAllRelatedData)
        {
            ProviderUtility.CheckParameter(ref username, true, true, true, 256, "username");

            using (
                var holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString)
                )
            {


                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    Application app = GetOrCreateApplication(ctx);
                    using (var tx = holder.StoreConnection.BeginTransaction())
                    {
                        try
                        {
                            AspUserView userV =
                                ctx.AspUsersView.FirstOrDefault(
                                    u => u.LoweredUserName == username.ToLower() && u.ApplicationId == app.ApplicationId);

                            if (userV == null)
                            {
                                return false;
                            }

                            AspUser user = ctx.AspUsers.First(u => u.UserId == userV.UserId);

                            FeatureTable toDelete = deleteAllRelatedData ? FeatureTable.All : FeatureTable.Membership;

                            if (toDelete.IsFlagged(FeatureTable.Membership))
                            {
                                AspMembershipUser userRecord =
                                    ctx.AspMembershipUsers.FirstOrDefault(u => u.UserId == userV.UserId);

                                if (userRecord != null)
                                {
                                    ctx.DeleteObject(userRecord);
                                }
                            }

                            if (toDelete.IsFlagged(FeatureTable.Roles))
                            {
                                user.Roles.Load();
                                user.Roles.Clear();
                            }

                            if (toDelete.IsFlagged(FeatureTable.Profile))
                            {
                                SQLite.Model.Profile profile = ctx.Profiles.FirstOrDefault(p => p.UserId == user.UserId);
                                if (profile != null)
                                {
                                    ctx.DeleteObject(profile);
                                }
                            }

                            if (toDelete.IsFlagged(FeatureTable.PersonalizationPerUser))
                            {
                                user.UserPaths.Load();
                                user.UserPaths.Clear();
                            }

                            if (toDelete.IsFlagged(FeatureTable.All))
                            {
                                AspUser userRecord = ctx.AspUsers.First(u => u.UserId == userV.UserId);
                                ctx.DeleteObject(userRecord);
                            }

                            ctx.SaveChanges();
                            tx.Commit();
                        }
                        catch (Exception ex)
                        {
                            tx.Rollback();
                            this.LogError(ex);
                            throw new ProviderException(GetExceptionText(MembershipErrorCode.ProviderError));
                        }
                    }
                }
            }

            return true;
        }

        /// <summary>
        /// Gets a collection of membership users where the e-mail address contains the specified e-mail address to match.
        /// </summary>
        /// <returns>
        /// A <see cref="T:System.Web.Security.MembershipUserCollection"/> collection that contains a page of 
        /// <paramref name="pageSize"/><see cref="T:System.Web.Security.MembershipUser"/> objects beginning at the page specified 
        /// by <paramref name="pageIndex"/>.
        /// </returns>
        /// <param name="emailToMatch">The e-mail address to search for.</param>
        /// <param name="pageIndex">
        /// The index of the page of results to return. <paramref name="pageIndex"/> is zero-based.
        /// </param>
        /// <param name="pageSize">The size of the page of results to return.</param>
        /// <param name="totalRecords">The total number of matched users.</param>
        /// <remarks>
        /// FindUsersByEmail returns a list of membership users where the user's e-mail address matches the supplied emailToMatch 
        /// for the configured ApplicationName property. If your data source supports additional search capabilities, such as 
        /// wildcard characters, you can provide more extensive search capabilities for e-mail addresses.
        /// 
        /// The results returned by FindUsersByEmail are constrained by the pageIndex and pageSize parameters. The pageSize 
        /// parameter identifies the number of MembershipUser objects to return in the MembershipUserCollection collection. The 
        /// pageIndex parameter identifies which page of results to return where 0 identifies the first page. The totalRecords 
        /// parameter is an out parameter that is set to the total number of membership users that matched the emailToMatch 
        /// value. For example, if 13 users were found where emailToMatch matched part of or the entire e-mail address, and the 
        /// pageIndex value was 1 with a pageSize of 5, then the MembershipUserCollection would contain the sixth through the 
        /// tenth users returned. totalRecords would be set to 13.
        /// </remarks>
        public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize,
                                                                  out int totalRecords)
        {
            ProviderUtility.CheckParameter(ref emailToMatch, false, false, false, 256, "emailToMatch");

            ProviderUtility.VerifyPagingParameters(pageIndex, pageSize);

            totalRecords = 0;
            MembershipUserCollection users = new MembershipUserCollection();

            using (var holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString))
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    try
                    {
                        Application app = GetOrCreateApplication(ctx);

                        IQueryable<AspUserMergedView> query =
                            ctx.AspUsersMergedView.Where(
                                u =>
                                u.ApplicationId == app.ApplicationId &&
                                u.LoweredEmail.Contains(emailToMatch.ToLower()));
                        totalRecords = query.Count();
                        IQueryable<AspUserMergedView> usersV =
                            query.OrderBy(
                                u => u.Email).Skip(pageSize * pageIndex).Take(pageSize);
                        foreach (AspUserMergedView userV in usersV)
                        {
                            users.Add(GetMembershipUser(userV));
                        }
                    }
                    catch (Exception ex)
                    {
                        this.LogError(ex);
                        throw new ProviderException(GetExceptionText(MembershipErrorCode.ProviderError));
                    }
                }
            }
            return users;
        }

        /// <summary>
        /// Gets a collection of membership users where the user name contains the specified user name to match.
        /// </summary>
        /// <returns>
        /// A <see cref="T:System.Web.Security.MembershipUserCollection"/> collection that contains a page of 
        /// <paramref name="pageSize"/><see cref="T:System.Web.Security.MembershipUser"/> objects beginning at the page specified
        /// by <paramref name="pageIndex"/>.
        /// </returns>
        /// <param name="usernameToMatch">The user name to search for.</param>
        /// <param name="pageIndex">
        /// The index of the page of results to return. 
        /// <paramref name="pageIndex"/> is zero-based.
        /// </param>
        /// <param name="pageSize">The size of the page of results to return.</param>
        /// <param name="totalRecords">The total number of matched users.</param>
        /// <remarks>
        /// FindUsersByName returns a list of membership users where the user name matches the supplied usernameToMatch for the 
        /// configured ApplicationName property. If your data source supports additional search capabilities, such as wildcard 
        /// characters, you can provide more extensive search capabilities for user names.
        /// 
        /// The results returned by FindUsersByName are constrained by the pageIndex and pageSize parameters. The pageSize 
        /// parameter identifies the number of MembershipUser objects to return in the MembershipUserCollection collection. 
        /// The pageIndex parameter identifies which page of results to return, where 0 identifies the first page. 
        /// The totalRecords parameter is an out parameter that is set to the total number of membership users that matched 
        /// the usernameToMatch value. For example, if 13 users were found where usernameToMatch matched part of or the entire 
        /// user name, and the pageIndex value was 1 with a pageSize of 5, then the MembershipUserCollection would contain 
        /// the sixth through the tenth users returned. totalRecords would be set to 13.
        /// </remarks>
        public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize,
                                                                 out int totalRecords)
        {
            ProviderUtility.CheckParameter(ref usernameToMatch, true, true, false, 256, "usernameToMatch");

            ProviderUtility.VerifyPagingParameters(pageIndex, pageSize);

            MembershipUserCollection users = new MembershipUserCollection();

            using (var holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString))
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    try
                    {
                        Application app = GetOrCreateApplication(ctx);
                        IOrderedQueryable<AspUserMergedView> query =
                            ctx.AspUsersMergedView.Where(u =>
                                                         u.ApplicationId == app.ApplicationId &&
                                                         u.LoweredUserName.Contains(usernameToMatch.ToLower())).
                                OrderBy(u => u.UserName);

                        totalRecords = query.Count();
                        IQueryable<AspUserMergedView> usersV = query.Skip(pageIndex * pageSize).Take(pageSize);
                        foreach (AspUserMergedView userV in usersV)
                        {
                            users.Add(GetMembershipUser(userV));
                        }
                    }
                    catch (Exception ex)
                    {
                        this.LogError(ex);
                        throw new ProviderException(GetExceptionText(MembershipErrorCode.ProviderError));
                    }
                }
            }

            return users;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public virtual string GeneratePassword()
        {
            return Membership.GeneratePassword(
                MinRequiredPasswordLength < PasswordSize ? PasswordSize : MinRequiredPasswordLength,
                MinRequiredNonAlphanumericCharacters);
        }

        /// <summary>
        /// Gets a collection of all the users in the data source in pages of data.
        /// GetAllUsers returns a list of all of the users from the data source for the configured ApplicationName property. Users 
        /// are returned in alphabetical order by user name.
        /// </summary>
        /// <returns>
        /// A <see cref="T:System.Web.Security.MembershipUserCollection"/> collection that contains a page of 
        /// <paramref name="pageSize"/><see cref="T:System.Web.Security.MembershipUser"/> objects beginning at the page specified 
        /// by <paramref name="pageIndex"/>.
        /// </returns>
        /// <param name="pageIndex">The index of the page of results to return. <paramref name="pageIndex"/> is zero-based.</param>
        /// <param name="pageSize">The size of the page of results to return.</param>
        /// <param name="totalRecords">The total number of matched users.</param>
        public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
        {
            ProviderUtility.VerifyPagingParameters(pageIndex, pageSize);

            MembershipUserCollection users = new MembershipUserCollection();
            totalRecords = 0;

            using (var holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString))
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    try
                    {
                        Application app = GetOrCreateApplication(ctx);
                        IQueryable<AspUserMergedView> query =
                            ctx.AspUsersMergedView.Where(u => u.ApplicationId == app.ApplicationId);
                        totalRecords = query.Count();
                        IQueryable<AspUserMergedView> page =
                            query.OrderBy(u => u.UserName).Skip(pageSize * pageIndex).Take(pageSize);
                        foreach (AspUserMergedView user in page)
                        {
                            users.Add(GetMembershipUser(user));
                        }
                    }
                    catch (Exception ex)
                    {
                        this.LogError(ex);
                        throw new ProviderException(GetExceptionText(MembershipErrorCode.ProviderError));
                    }
                }
            }

            return users;
        }

        /// <summary>
        /// Gets the number of users currently accessing the application.
        /// </summary>
        /// <returns>
        /// The number of users currently accessing the application.
        /// </returns>
        /// <remarks>
        /// Returns an integer value that is the count of all the users in the data source where the LastActivityDate is greater 
        /// than the current date and time minus the UserIsOnlineTimeWindow. The UserIsOnlineTimeWindow is a positive integer value 
        /// specifying the number of minutes to use when determining whether a user is online.
        /// </remarks>
        public override int GetNumberOfUsersOnline()
        {

            using (var holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString))
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    try
                    {
                        Application app = GetOrCreateApplication(ctx);

                        DateTime window = ProviderUtility.Now().AddMinutes(-Membership.UserIsOnlineTimeWindow);

                        int count =
                            ctx.AspUsersMergedView.Where(
                                u => u.ApplicationId == app.ApplicationId && u.LastActivityDate > window).Count();

                        return count;
                    }
                    catch (Exception ex)
                    {
                        this.LogError(ex);
                        throw new ProviderException(GetExceptionText(MembershipErrorCode.ProviderError));
                    }
                }
            }

        }

        /// <summary>
        /// Gets the password for the specified user name from the data source.
        /// </summary>
        /// <returns>
        /// The password for the specified user name.
        /// </returns>
        /// <param name="username">The user to retrieve the password for. </param>
        /// <param name="answer">The password answer for the user. </param>
        /// <remarks>
        /// Takes, as input, a user name and a password answer and retrieves the password for that user from the data source and 
        /// returns the password as a string.
        /// 
        /// The GetPassword method ensures that the EnablePasswordRetrieval flag is set to true before performing any action. 
        /// If EnablePasswordRetrieval is false, a NotSupportedException exception is thrown.
        /// 
        /// GetPassword also checks the value of the RequiresQuestionAndAnswer property. If RequiresQuestionAndAnswer is true, 
        /// GetPassword checks the value of the supplied answer parameter against the stored password answer in the data source. 
        /// If they do not match, a MembershipPasswordException exception is thrown.
        /// 
        /// If your custom membership provider supports hashed passwords, the GetPassword method should return an exception if 
        /// the EnablePasswordRetrieval property is set to true and the password format is set to Hashed. 
        /// Hashed passwords cannot be retrieved.
        /// </remarks>
        [Description("COMPLETE-NEED ENCRYPTED CONFIG TO TEST")]
        public override string GetPassword(string username, string answer)
        {
            if (!EnablePasswordRetrieval)
            {
                throw new NotSupportedException(SR.GetString(SR.MembershipPasswordRetrievalNotSupported));
            }

            ProviderUtility.CheckParameter(ref username, true, true, true, 256, "username");

            string encodedPasswordAnswer = GetEncodedPasswordAnswer(username, answer);
            ProviderUtility.CheckParameter(ref encodedPasswordAnswer, RequiresQuestionAndAnswer, RequiresQuestionAndAnswer,
                                      false, 128, "passwordAnswer");

            int passwordFormat;
            MembershipErrorCode status;

            string pass = GetPasswordFromDB(username, encodedPasswordAnswer, out passwordFormat, out status);

            if (pass == null)
            {
                string errText = GetExceptionText(status);
                if (IsStatusDueToBadPassword(status))
                {
                    throw new MembershipPasswordException(errText);
                }
                throw new ProviderException(errText);
            }

            return UnEncodePassword(pass, passwordFormat);
        }

        /// <summary>
        /// Gets information from the data source for a user. Provides an option to update the last-activity date/time stamp for the user.
        /// </summary>
        /// <returns>
        /// A <see cref="T:System.Web.Security.MembershipUser"/> object populated with the specified user's information from the data source.
        /// </returns>
        /// <param name="userName">The name of the user to get information for. </param>
        /// <param name="userIsOnline">
        /// true to update the last-activity date/time stamp for the user; false to return user information without updating the last-activity 
        /// date/time stamp for the user. 
        /// </param>
        /// <remarks>
        /// Takes, as input, a user name and a Boolean value indicating whether to update the LastActivityDate property value for the user to 
        /// show that the user is currently online. GetUser returns a MembershipUser object populated with current values from the data source for 
        /// the specified user. If the user name is not found in the data source, GetUser returns null.
        /// </remarks>
        public override MembershipUser GetUser(string userName, bool userIsOnline)
        {
            ProviderUtility.CheckParameter(ref userName, true, false, true, 256, "userName");

            MembershipUser returnValue = null;

            using (var holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString))
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    using (var tx = holder.Connection.BeginTransaction())
                    {
                        try
                        {
                            Application app = GetOrCreateApplication(ctx);
                            if (app != null)
                            {
                                AspUserMergedView userV = ctx.AspUsersMergedView.FirstOrDefault(
                                        u => u.LoweredUserName == userName.ToLower() && u.ApplicationId == app.ApplicationId);
                                if (userV != null)
                                {
                                    if (userIsOnline)
                                    {
                                        AspUser user = ctx.AspUsers.First(u => u.UserId == userV.UserId);
                                        user.LastActivityDate = ProviderUtility.Now();
                                        ctx.SaveChanges();
                                        userV.LastActivityDate = user.LastActivityDate;
                                    }
                                    returnValue = GetMembershipUser(userV);
                                }
                            }

                            tx.Commit();
                        }
                        catch (Exception ex)
                        {
                            tx.Rollback();
                            this.LogError(ex);
                            throw new ProviderException(GetExceptionText(MembershipErrorCode.ProviderError));
                        }
                    }
                }
            }


            return returnValue;
        }

        /// <summary>
        /// Gets user information from the data source based on the unique identifier for the membership user. Provides an option 
        /// to update the last-activity date/time stamp for the user.
        /// </summary>
        /// <returns>
        /// A <see cref="T:System.Web.Security.MembershipUser"/> object populated with the specified user's information from the 
        /// data source.
        /// </returns>
        /// <param name="providerUserKey">The unique identifier for the membership user to get information for.</param>
        /// <param name="userIsOnline">true to update the last-activity date/time stamp for the user; false to return user 
        /// information without updating the last-activity date/time stamp for the user.</param>
        /// <remarks>
        /// GetUser Method (Object, Boolean)
        /// Takes, as input, a unique identifier for a user record and a Boolean value indicating whether to update the 
        /// LastActivityDate value for the user to show that the user is currently online. The GetUser method returns a 
        /// MembershipUser object populated with current values from the data source for the specified user. 
        /// If the user name is not found in the data source, GetUser returns null.
        /// </remarks>
        public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
        {
            if (providerUserKey == null)
            {
                throw new ArgumentNullException("providerUserKey");
            }
            if (!(providerUserKey is Guid))
            {
                throw new ArgumentException(SR.GetString(SR.MembershipInvalidProviderUserKey), "providerUserKey");
            }

            Guid userId = (Guid)providerUserKey;
            MembershipUser returnValue = null;

            using (var holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString))
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    using (var tx = holder.Connection.BeginTransaction())
                    {
                        try
                        {
#pragma warning disable 168
                            Application app = GetOrCreateApplication(ctx);
#pragma warning restore 168

                            AspUserMergedView userV = ctx.AspUsersMergedView.FirstOrDefault(u => u.UserId == userId);

                            if (userV != null)
                            {
                                if (userIsOnline)
                                {
                                    AspUser user2 = ctx.AspUsers.FirstOrDefault(u => u.UserId == userId);
                                    user2.LastActivityDate = ProviderUtility.Now();
                                    ctx.SaveChanges();
                                    userV.LastActivityDate = user2.LastActivityDate;
                                }

                                returnValue = GetMembershipUser(userV);
                            }


                            tx.Commit();
                        }
                        catch (Exception ex)
                        {
                            tx.Rollback();
                            this.LogError(ex);
                            throw new ProviderException(GetExceptionText(MembershipErrorCode.ProviderError));
                        }
                    }
                }
            }



            return returnValue;
        }

        /// <summary>
        /// Gets the user name associated with the specified e-mail address.
        /// </summary>
        /// <returns>
        /// The user name associated with the specified e-mail address. If no match is found, return null.
        /// </returns>
        /// <param name="email">The e-mail address to search for. </param>
        /// <remarks>
        /// GetUserNameByEmail 
        /// Takes, as input, an e-mail address and returns the first user name from the data source where the e-mail address 
        /// matches the supplied email parameter value.
        /// 
        /// If no user name is found with a matching e-mail address, null is returned.
        /// 
        /// If multiple user names are found that match a particular e-mail address, only the first user name found should be 
        /// returned. You can implement a custom mechanism for ensuring a unique e-mail address for each user name such as the 
        /// RequiresUniqueEmail property supported by the SqlMembershipProvider provider.
        /// </remarks>
        public override string GetUserNameByEmail(string email)
        {
            ProviderUtility.CheckParameter(ref email, false, false, false, 256, "email");

            using (var holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString))
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    Application app = GetOrCreateApplication(ctx);
                    AspUserMergedView userV =
                        ctx.AspUsersMergedView.FirstOrDefault(
                            u => u.ApplicationId == app.ApplicationId && u.LoweredEmail == email.ToLower());

                    return userV == null ? null : userV.UserName;
                }
            }
        }

        /// <summary>
        /// Initializes the provider.
        /// </summary>
        /// <param name="name">
        /// The friendly name of the provider.
        /// </param>
        /// <param name="config">
        /// A collection of the name/value pairs representing the provider-specific attributes specified in the configuration 
        /// for this provider.
        /// </param>
        /// <exception cref="T:System.ArgumentNullException">
        /// The name of the provider is null.
        /// </exception>
        /// <exception cref="T:System.ArgumentException">
        /// The name of the provider has a length of zero.
        /// </exception>
        /// <exception cref="T:System.InvalidOperationException">
        /// An attempt is made to call 
        /// <see cref="M:System.Configuration.Provider.ProviderBase.Initialize(System.String,System.Collections.Specialized.NameValueCollection)" /> 
        /// on a provider after the provider has already been initialized.
        /// </exception>
        public override void Initialize(string name, NameValueCollection config)
        {
            if (config == null)
            {
                throw new ArgumentNullException("config");
            }

            if (String.IsNullOrEmpty(name))
            {
                name = "SQLiteMembershipProvider";
            }

            if (string.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", SR.GetString(SR.MembershipSqlProviderDescription));
            }

            base.Initialize(name, config);



            _enablePasswordRetrieval = ProviderUtility.GetBooleanValue(config, "enablePasswordRetrieval", false);
            _enablePasswordReset = ProviderUtility.GetBooleanValue(config, "enablePasswordReset", true);
            _requiresQuestionAndAnswer = ProviderUtility.GetBooleanValue(config, "requiresQuestionAndAnswer", true);
            _requiresUniqueEmail = ProviderUtility.GetBooleanValue(config, "requiresUniqueEmail", true);
            _maxInvalidPasswordAttempts = ProviderUtility.GetIntValue(config, "maxInvalidPasswordAttempts", 5, false, 0);
            _passwordAttemptWindow = ProviderUtility.GetIntValue(config, "passwordAttemptWindow", 10, false, 0);
            _minRequiredPasswordLength = ProviderUtility.GetIntValue(config, "minRequiredPasswordLength", 7, false, 128);
            _minRequiredNonAlphanumericCharacters = ProviderUtility.GetIntValue(config,
                                                                           "minRequiredNonalphanumericCharacters", 1,
                                                                           true, 128);

            _passwordStrengthRegularExpression = config["passwordStrengthRegularExpression"];
            if (!string.IsNullOrEmpty(_passwordStrengthRegularExpression))
            {
                _passwordStrengthRegularExpression = _passwordStrengthRegularExpression.Trim();
                if (_passwordStrengthRegularExpression.Length != 0)
                {
                    try
                    {
                        new Regex(_passwordStrengthRegularExpression);
                    }
                    catch (ArgumentException e)
                    {
                        throw new ProviderException(e.Message, e);
                    }
                }
            }
            else
            {
                _passwordStrengthRegularExpression = string.Empty;
            }
            if (_minRequiredNonAlphanumericCharacters > _minRequiredPasswordLength)
            {
                throw new HttpException(
                    SR.GetString(SR.MinRequiredNonalphanumericCharactersCanNotBeMoreThanMinRequiredPasswordLength));
            }

            _applicationName = config["applicationName"];
            if (string.IsNullOrEmpty(_applicationName))
            {
                _applicationName = ProviderUtility.GetDefaultAppName();
            }

            if (_applicationName.Length > 256)
            {
                throw new ProviderException(SR.GetString(SR.ProviderApplicationNameTooLong));
            }

            string strTemp = config["passwordFormat"] ?? "Hashed";

            switch (strTemp)
            {
                case "Clear":
                    _passwordFormat = MembershipPasswordFormat.Clear;
                    break;
                case "Encrypted":
                    _passwordFormat = MembershipPasswordFormat.Encrypted;
                    break;
                case "Hashed":
                    _passwordFormat = MembershipPasswordFormat.Hashed;
                    break;
                default:
                    throw new ProviderException(SR.GetString(SR.ProviderBadPasswordFormat));
            }

            if (PasswordFormat == MembershipPasswordFormat.Hashed && EnablePasswordRetrieval)
            {
                throw new ProviderException(SR.GetString(SR.ProviderCanNotRetrieveHashedPassword));
            }

            PropertyInfo pinfo = typeof(MachineKeySection).GetProperty("IsDecryptionKeyAutogenerated",
                                                                        BindingFlags.Static | BindingFlags.NonPublic |
                                                                        BindingFlags.GetProperty);
            bool isDecryptionKeyAutogenerated = (bool)pinfo.GetValue(null, null);

            if (_passwordFormat == MembershipPasswordFormat.Encrypted && isDecryptionKeyAutogenerated)
            {
                throw new ProviderException(SR.GetString(SR.CanNotUseEncryptedPasswordsWithAutogenKeys));
            }

            _entityConnectionString = config["connectionStringName"];

            if (string.IsNullOrEmpty(_entityConnectionString))
            {
                throw new ProviderException(SR.GetString(SR.ConnectionNameNotSpecified));
            }

            SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString);

            config.Remove("connectionStringName");
            config.Remove("enablePasswordRetrieval");
            config.Remove("enablePasswordReset");
            config.Remove("requiresQuestionAndAnswer");
            config.Remove("applicationName");
            config.Remove("requiresUniqueEmail");
            config.Remove("maxInvalidPasswordAttempts");
            config.Remove("passwordAttemptWindow");
            config.Remove("passwordFormat");
            config.Remove("name");
            config.Remove("minRequiredPasswordLength");
            config.Remove("minRequiredNonalphanumericCharacters");
            config.Remove("passwordStrengthRegularExpression");
            if (config.Count > 0)
            {
                string attribUnrecognized = config.GetKey(0);
                if (!String.IsNullOrEmpty(attribUnrecognized))
                    throw new ProviderException(SR.GetString(SR.ProviderUnrecognizedAttribute, attribUnrecognized));
            }

            using (
                var holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString)
                )
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    GetOrCreateApplication(ctx);
                }
            }
        }

        /// <summary>
        /// Resets a user's password to a new, automatically generated password.
        /// </summary>
        /// <returns>
        /// The new password for the specified user.
        /// </returns>
        /// <param name="username">The user to reset the password for. </param>
        /// <param name="answer">The password answer for the specified user. </param>
        /// <remarks>
        /// ResetPassword 
        /// Takes, as input, a user name and a password answer and generates a new, random password for the specified user.
        /// 
        /// Note:
        /// The random password created by the ResetPassword method is not guaranteed to pass the regular expression in the 
        /// PasswordStrengthRegularExpression property. However, the random password will meet the criteria established by the 
        /// MinRequiredPasswordLength and MinRequiredNonAlphanumericCharacters properties. The ResetPassword method updates the user 
        /// information in the data source with the new password value and returns the new password as a string. A convenient 
        /// mechanism for generating a random password is the GeneratePassword method.
        /// 
        /// ResetPassword ensures that the EnablePasswordReset flag is set to true before performing any action. If 
        /// EnablePasswordReset is false, a NotSupportedException exception is thrown.
        /// 
        /// ResetPassword also checks the value of the RequiresQuestionAndAnswer property. If RequiresQuestionAndAnswer is true, 
        /// ResetPassword checks the value of the supplied answer parameter against the stored password answer in the data source. 
        /// If they do not match, a MembershipPasswordException exception is thrown.
        /// </remarks>
        public override string ResetPassword(string username, string answer)
        {
            if (!EnablePasswordReset)
            {
                throw new NotSupportedException(SR.GetString(SR.NotConfiguredToSupportPasswordResets));
            }

            ProviderUtility.CheckParameter(ref username, true, true, true, 256, "username");

            string salt;
            int passwordFormat;
            string passwdFromDB;
            MembershipErrorCode status;
            int failedPasswordAttemptCount;
            int failedPasswordAnswerAttemptCount;
            bool isApproved;
            DateTime lastLoginDate, lastActivityDate;

            GetPasswordWithFormat(username, false, out status, out passwdFromDB, out passwordFormat, out salt,
                                  out failedPasswordAttemptCount,
                                  out failedPasswordAnswerAttemptCount, out isApproved, out lastLoginDate,
                                  out lastActivityDate);
            if (status != MembershipErrorCode.OK)
            {
                if (IsStatusDueToBadPassword(status))
                {
                    throw new MembershipPasswordException(GetExceptionText(status));
                }
                throw new ProviderException(GetExceptionText(status));
            }

            string encodedPasswordAnswer;

            if (answer != null)
            {
                answer = answer.Trim();
            }

            if (!string.IsNullOrEmpty(answer))
            {
                encodedPasswordAnswer = EncodePassword(answer.ToLower(CultureInfo.InvariantCulture), passwordFormat,
                                                       salt);
            }
            else
            {
                encodedPasswordAnswer = answer;
            }

            ProviderUtility.CheckParameter(ref encodedPasswordAnswer, RequiresQuestionAndAnswer, RequiresQuestionAndAnswer,
                                      false, 128, "passwordAnswer");

            string newPassword = GeneratePassword();

            ValidatePasswordEventArgs e = new ValidatePasswordEventArgs(username, newPassword, false);

            OnValidatingPassword(e);

            if (e.Cancel)
            {
                if (e.FailureInformation != null)
                {
                    throw e.FailureInformation;
                }
                throw new ProviderException(SR.GetString(SR.MembershipCustomPasswordValidationFailure));
            }

            // TODO: review the SPROC carefully and verify this block

            using (var holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString))
            {


                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    Application app = GetOrCreateApplication(ctx);

                    using (var tx = holder.StoreConnection.BeginTransaction())
                    {
                        try
                        {
                            AspUserMergedView userV =
                                ctx.AspUsersMergedView.FirstOrDefault(
                                    u => u.ApplicationId == app.ApplicationId && u.LoweredUserName == username.ToLower());
                            if (userV == null)
                            {
                                status = MembershipErrorCode.MembershipUserNotFound;
                            }
                            else
                            {
                                AspMembershipUser muser = ctx.AspMembershipUsers.First(u => u.UserId == userV.UserId);

                                if (muser.IsLockedOut)
                                {
                                    status = MembershipErrorCode.MembershipAccountLockOut;
                                }
                                else
                                {
                                    if (encodedPasswordAnswer.ToLower() != muser.PasswordAnswer.ToLower())
                                    {
                                        muser.FailedPasswordAnswerAttemptWindowStart = ProviderUtility.Now();
                                        if (ProviderUtility.Now() >
                                            muser.FailedPasswordAnswerAttemptWindowStart.AddMinutes(
                                                PasswordAttemptWindow))
                                        {
                                            muser.FailedPasswordAnswerAttemptCount = 1;
                                        }
                                        else
                                        {
                                            muser.FailedPasswordAnswerAttemptCount =
                                                muser.FailedPasswordAnswerAttemptCount + 1;
                                        }
                                        if (muser.FailedPasswordAnswerAttemptCount >= MaxInvalidPasswordAttempts)
                                        {
                                            muser.IsLockedOut = true;
                                            muser.LastLockoutDate = ProviderUtility.Now();
                                        }

                                        status = MembershipErrorCode.MembershipWrongAnswer;
                                    }
                                    else
                                    {
                                        muser.FailedPasswordAnswerAttemptCount = 0;

                                        muser.FailedPasswordAnswerAttemptWindowStart = NullDate;
                                        status = MembershipErrorCode.OK;
                                    }

                                    if (status == MembershipErrorCode.OK)
                                    {
                                        muser.Password = EncodePassword(newPassword, passwordFormat, salt);
                                        muser.LastPasswordChangedDate = ProviderUtility.Now();
                                        muser.PasswordFormat = passwordFormat;
                                        muser.PasswordSalt = salt;
                                    }

                                }
                            }
                            ctx.SaveChanges();
                            tx.Commit();
                        }
                        catch (Exception ex)
                        {
                            tx.Rollback();
                            throw new ProviderException("Error", ex);
                        }
                    }
                }
            }

            if (status != MembershipErrorCode.OK)
            {
                string errText = GetExceptionText(status);

                if (IsStatusDueToBadPassword(status))
                {
                    throw new MembershipPasswordException(errText);
                }

                throw new ProviderException(errText);
            }
            return newPassword;
        }

        /// <summary>
        /// Clears a lock so that the membership user can be validated.
        /// </summary>
        /// <returns>
        /// true if the membership user was successfully unlocked; otherwise, false.
        /// </returns>
        /// <param name="userName">The membership user whose lock status you want to clear.</param>
        /// <remarks>
        /// UnlockUser 
        /// Takes, as input, a user name, and updates the field in the data source that stores the IsLockedOut property to false. 
        /// The UnlockUser method returns true if the record for the membership user is updated successfully; otherwise, false.
        /// </remarks>
        public override bool UnlockUser(string userName)
        {
            ProviderUtility.CheckParameter(ref userName, true, true, true, 256, "userName");
            MembershipErrorCode status;
            using (var holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString))
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    using (var tx = holder.Connection.BeginTransaction())
                    {
                        try
                        {
                            Application app = GetOrCreateApplication(ctx);

                            AspUserMergedView userV =
                                ctx.AspUsersMergedView.FirstOrDefault(
                                    u => u.ApplicationId == app.ApplicationId && u.LoweredUserName == userName.ToLower());

                            if (userV != null)
                            {
                                AspMembershipUser muser = ctx.AspMembershipUsers.First(u => u.UserId == userV.UserId);
                                muser.IsLockedOut = false;
                                muser.FailedPasswordAttemptCount = 0;
                                muser.FailedPasswordAttemptWindowStart = NullDate;
                                muser.FailedPasswordAnswerAttemptCount = 0;
                                muser.FailedPasswordAnswerAttemptWindowStart = NullDate;
                                muser.LastLockoutDate = NullDate;

                                status = MembershipErrorCode.OK;

                            }
                            else
                            {
                                status = MembershipErrorCode.MembershipUserNotFound;
                            }

                            ctx.SaveChanges();
                            tx.Commit();

                        }
                        catch (Exception ex)
                        {
                            tx.Rollback();
                            this.LogError(ex);
                            throw new ProviderException(GetExceptionText(MembershipErrorCode.ProviderError));
                        }
                    }




                }
            }

            return status == MembershipErrorCode.OK;
        }



        /// <summary>
        /// Updates information about a user in the data source.
        /// </summary>
        /// <param name="user">A <see cref="T:System.Web.Security.MembershipUser"/> object that represents the user to update 
        /// and the updated information for the user. </param>
        /// <remarks>
        /// UpdateUser 
        /// Takes, as input, a MembershipUser object populated with user information and updates the data source with the 
        /// supplied values.
        /// </remarks>
        public override void UpdateUser(MembershipUser user)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }

            string temp = user.UserName;
            ProviderUtility.CheckParameter(ref temp, true, true, true, 256, "UserName");
            temp = user.Email;
            ProviderUtility.CheckParameter(ref temp, RequiresUniqueEmail, RequiresUniqueEmail, false, 256, "Email");
            user.Email = temp;

            MembershipErrorCode status = MembershipErrorCode.OK;

            using (var holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString))
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    using (var tx = holder.StoreConnection.BeginTransaction())
                    {
                        try
                        {
                            Guid userId = (Guid)user.ProviderUserKey;
                            AspMembershipUser muser = ctx.AspMembershipUsers.FirstOrDefault(u => u.UserId == userId);
                            AspUser auser = ctx.AspUsers.FirstOrDefault(u => u.UserId == userId);

                            if (muser == null || auser == null)
                            {
                                status = MembershipErrorCode.MembershipUserNotFound;
                            }
                            else
                            {
                                auser.LastActivityDate = ProviderUtility.Now();

                                if (RequiresUniqueEmail)
                                {
                                    if (
                                        ctx.AspMembershipUsers.Any(
                                            u =>
                                            u.LoweredEmail == user.Email.ToLower() && u.UserId != userId))
                                    {
                                        status = MembershipErrorCode.MembershipInvalidEmail;
                                    }
                                }

                                if (status == MembershipErrorCode.OK)
                                {
                                    muser.Email = user.Email;
                                    muser.LoweredEmail = user.Email.ToLower();
                                    muser.Comment = user.Comment;
                                    muser.IsApproved = user.IsApproved;
                                    muser.LastLoginDate = user.LastLoginDate.ToUniversalTime();

                                }

                            }
                            ctx.SaveChanges();
                            tx.Commit();
                        }
                        catch (Exception ex)
                        {
                            tx.Rollback();
                            this.LogError(ex);
                            throw new ProviderException(GetExceptionText(MembershipErrorCode.ProviderError));
                        }

                    }
                }
            }

            if (status != MembershipErrorCode.OK)
            {
                throw new ProviderException(GetExceptionText(status));
            }
        }



        /// <summary>
        /// Verifies that the specified user name and password exist in the data source.
        /// </summary>
        /// <returns>
        /// true if the specified username and password are valid; otherwise, false.
        /// </returns>
        /// <param name="username">The name of the user to validate. </param>
        /// <param name="password">The password for the specified user. </param>
        /// <remarks>
        /// ValidateUser 
        /// Takes, as input, a user name and a password and verifies that the values match those in the data source. ValidateUser 
        /// returns true for a successful user name and password match; otherwise, false.
        /// 
        /// For successful validations, ValidateUser updates the LastLoginDate for the specified user.
        /// 
        /// NOTE: Not sure why MS sealed and internalized the system events and perf counters for these
        /// events. Doing so forces writing custom code to audit and monitor these events. The provider model
        /// is intended to abstract the provider implementation and enable common usage NOT to induce custom
        /// consumer code. WTF?! So, I just whip out the can opener and expose them. Sue me.
        /// </remarks>
        public override bool ValidateUser(string username, string password)
        {



            if (ProviderUtility.ValidateParameter(ref username, true, true, true, 256) &&
                ProviderUtility.ValidateParameter(ref password, true, true, false, 128) &&
                CheckPassword(username, password, true, true))
            {


                ProviderUtility.PerfCountersIncrementCounter.Invoke(null, new object[] { ProviderUtility.AppPerfCounterMemberSuccess });
                // Comment out perf counters in sample: PerfCounters.IncrementCounter(MEMBER_SUCCESS);
                ProviderUtility.RaiseSystemEvent(null, WebEventCodes.AuditMembershipAuthenticationSuccess, username);
                //_systemWebEventInvoker.Invoke(null, new object[] { null, WebEventCodes.AuditMembershipAuthenticationSuccess, username });

                return true;
            }

            //PerfCounters.IncrementCounter(MEMBER_FAIL);
            ProviderUtility.PerfCountersIncrementCounter.Invoke(null, new object[] { ProviderUtility.AppPerfCounterMemberFail });
            ProviderUtility.RaiseSystemEvent(null, WebEventCodes.AuditMembershipAuthenticationFailure, username);
            //_systemWebEventInvoker.Invoke(null, new object[] { null, WebEventCodes.AuditMembershipAuthenticationFailure, username });


            return false;
        }

        #endregion

        #region Private Methods

        private bool CheckPassword(string username, string password, bool updateLastLoginActivityDate,
                                           bool failIfNotApproved, out string salt, out int passwordFormat)
        {
            string passwdFromDB;
            MembershipErrorCode status;
            int failedPasswordAttemptCount;
            int failedPasswordAnswerAttemptCount;
            bool isApproved;
            DateTime lastLoginDate, lastActivityDate;

            GetPasswordWithFormat(username, updateLastLoginActivityDate, out status, out passwdFromDB,
                                  out passwordFormat, out salt, out failedPasswordAttemptCount,
                                  out failedPasswordAnswerAttemptCount, out isApproved, out lastLoginDate,
                                  out lastActivityDate);

            if (status != MembershipErrorCode.OK)
                return false;
            if (!isApproved && failIfNotApproved)
                return false;

            string encodedPasswd = EncodePassword(password, passwordFormat, salt);

            bool isPasswordCorrect = passwdFromDB.Equals(encodedPasswd);

            if (isPasswordCorrect && failedPasswordAttemptCount == 0 && failedPasswordAnswerAttemptCount == 0)
                return true;
            using (
                var holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString)
                )
            {


                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    Application app = GetOrCreateApplication(ctx);
                    using (var tx = holder.StoreConnection.BeginTransaction())
                    {
                        AspUserMergedView userV =
                            ctx.AspUsersMergedView.FirstOrDefault(
                                u => u.LoweredUserName == username.ToLower() && u.ApplicationId == app.ApplicationId);

                        if (userV != null && !userV.IsLockedOut)
                        {
                            AspMembershipUser muser = ctx.AspMembershipUsers.First(u => u.UserId == userV.UserId);
                            if (!isPasswordCorrect)
                            {
                                muser.FailedPasswordAttemptWindowStart = ProviderUtility.Now();

                                if (ProviderUtility.Now() >
                                    muser.FailedPasswordAttemptWindowStart.AddMinutes(PasswordAttemptWindow))
                                {
                                    muser.FailedPasswordAttemptCount = 1;
                                }
                                else
                                {
                                    muser.FailedPasswordAttemptCount = muser.FailedPasswordAttemptCount + 1;
                                }

                                if (userV.FailedPasswordAttemptCount >= MaxInvalidPasswordAttempts)
                                {
                                    muser.IsLockedOut = true;
                                    muser.LastLockoutDate = ProviderUtility.Now();
                                }
                            }
                            else
                            {
                                muser.FailedPasswordAttemptCount = 0;
                                muser.FailedPasswordAttemptWindowStart = NullDate;
                                muser.FailedPasswordAnswerAttemptCount = 0;
                                muser.FailedPasswordAnswerAttemptWindowStart = NullDate;
                                muser.LastLockoutDate = NullDate;
                            }

                            if (updateLastLoginActivityDate)
                            {
                                AspUser user = ctx.AspUsers.First(u => u.UserId == userV.UserId);
                                user.LastActivityDate = ProviderUtility.Now();
                                muser.LastLoginDate = ProviderUtility.Now();
                            }

                            try
                            {
                                ctx.SaveChanges();
                                tx.Commit();
                            }
                            catch
                            {
                                tx.Rollback();
                                throw;
                            }
                        }
                    }
                }
            }
            return isPasswordCorrect;
        }

        private bool CheckPassword(string username, string password, bool updateLastLoginActivityDate,
                                           bool failIfNotApproved)
        {
            string salt;
            int passwordFormat;
            return CheckPassword(username, password, updateLastLoginActivityDate, failIfNotApproved, out salt,
                                 out passwordFormat);
        }

        private string EncodePassword(string pass, int passwordFormat, string salt)
        {
            if (passwordFormat == 0) // MembershipPasswordFormat.Clear
                return pass;

            byte[] bIn = Encoding.Unicode.GetBytes(pass);
            byte[] bSalt = Convert.FromBase64String(salt);
            byte[] bAll = new byte[bSalt.Length + bIn.Length];
            byte[] bRet;

            Buffer.BlockCopy(bSalt, 0, bAll, 0, bSalt.Length);
            Buffer.BlockCopy(bIn, 0, bAll, bSalt.Length, bIn.Length);
            if (passwordFormat == 1)
            {
                // MembershipPasswordFormat.Hashed
                HashAlgorithm s = HashAlgorithm.Create(Membership.HashAlgorithmType);
                bRet = s.ComputeHash(bAll);
            }
            else
            {
                bRet = EncryptPassword(bAll);
            }

            return Convert.ToBase64String(bRet);
        }

        private static string GenerateSalt()
        {
            byte[] buf = new byte[16];
            (new RNGCryptoServiceProvider()).GetBytes(buf);
            return Convert.ToBase64String(buf);
        }

        private string GetEncodedPasswordAnswer(string username, string passwordAnswer)
        {
            if (passwordAnswer != null)
            {
                passwordAnswer = passwordAnswer.Trim();
            }
            if (string.IsNullOrEmpty(passwordAnswer))
                return passwordAnswer;
            MembershipErrorCode status;
            int passwordFormat, failedPasswordAttemptCount, failedPasswordAnswerAttemptCount;
            string password, passwordSalt;
            bool isApproved;
            DateTime lastLoginDate, lastActivityDate;
            GetPasswordWithFormat(username, false, out status, out password, out passwordFormat, out passwordSalt,
                                  out failedPasswordAttemptCount, out failedPasswordAnswerAttemptCount, out isApproved,
                                  out lastLoginDate, out lastActivityDate);
            if (status == MembershipErrorCode.OK)
            {
                return EncodePassword(passwordAnswer.ToLower(CultureInfo.InvariantCulture), passwordFormat, passwordSalt);
            }
            throw new ProviderException(GetExceptionText(status));
        }

        private static string GetExceptionText(MembershipErrorCode status)
        {
            string key;
            switch (status)
            {
                case MembershipErrorCode.OK:
                    return String.Empty;
                case MembershipErrorCode.MembershipUserNotFound:
                    key = SR.MembershipUserNotFound;
                    break;
                case MembershipErrorCode.MembershipWrongPassword:
                    key = SR.MembershipWrongPassword;
                    break;
                case MembershipErrorCode.MembershipWrongAnswer:
                    key = SR.MembershipWrongAnswer;
                    break;
                case MembershipErrorCode.MembershipInvalidPassword:
                    key = SR.MembershipInvalidPassword;
                    break;
                case MembershipErrorCode.MembershipInvalidQuestion:
                    key = SR.MembershipInvalidQuestion;
                    break;
                case MembershipErrorCode.MembershipInvalidAnswer:
                    key = SR.MembershipInvalidAnswer;
                    break;
                case MembershipErrorCode.MembershipInvalidEmail:
                    key = SR.MembershipInvalidEmail;
                    break;
                case MembershipErrorCode.MembershipAccountLockOut:
                    key = SR.MembershipAccountLockOut;
                    break;
                default:
                    key = SR.ProviderError;
                    break;
            }
            return SR.GetString(key);
        }

        private MembershipUser GetMembershipUser(AspUserMergedView user)
        {
            return new MembershipUser(Name, user.UserName, user.UserId, user.Email,
                                      user.PasswordQuestion, user.Comment, user.IsApproved,
                                      user.IsLockedOut, user.CreateDate.ToLocalTime(),
                                      user.LastLoginDate.ToLocalTime(),
                                      user.LastActivityDate.GetValueOrDefault().ToLocalTime(),
                                      user.LastPasswordChangedDate.ToLocalTime(),
                                      user.LastLockoutDate.ToLocalTime());
        }

        private Application GetOrCreateApplication(AspNetDbEntities entities)
        {
            return ProviderUtility.GetOrCreateApplication(entities, ApplicationName);
        }

        private string GetPasswordFromDB(string username, string passwordAnswer, out int passwordFormat,
                                                 out MembershipErrorCode status)
        {
            status = MembershipErrorCode.OK;
            passwordFormat = 0;
            string password = null;

            using (var holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString))
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    Application app = GetOrCreateApplication(ctx);

                    AspUserMergedView userV =
                        ctx.AspUsersMergedView.FirstOrDefault(
                            u => u.ApplicationId == app.ApplicationId && u.LoweredUserName == username.ToLower());

                    if (userV == null)
                    {
                        status = MembershipErrorCode.MembershipUserNotFound;
                    }
                    else if (userV.IsLockedOut)
                    {
                        status = MembershipErrorCode.MembershipAccountLockOut;
                    }
                    else
                    {
                        AspMembershipUser muser = ctx.AspMembershipUsers.First(u => u.UserId == userV.UserId);
                        if (!string.IsNullOrEmpty(passwordAnswer))
                        {
                            if (string.IsNullOrEmpty(muser.PasswordAnswer) ||
                                (muser.PasswordAnswer.ToLower() != passwordAnswer.ToLower()))
                            {
                                muser.FailedPasswordAnswerAttemptWindowStart = ProviderUtility.Now();

                                if (ProviderUtility.Now() >
                                    userV.FailedPasswordAnswerAttemptWindowStart.AddMinutes(PasswordAttemptWindow))
                                {
                                    muser.FailedPasswordAnswerAttemptCount = 1;
                                }
                                else
                                {
                                    muser.FailedPasswordAnswerAttemptCount =
                                        muser.FailedPasswordAnswerAttemptCount + 1;
                                }
                                if (muser.FailedPasswordAnswerAttemptCount > MaxInvalidPasswordAttempts)
                                {
                                    muser.IsLockedOut = true;
                                    muser.LastLockoutDate = ProviderUtility.Now();
                                }
                                status = MembershipErrorCode.MembershipWrongAnswer;
                            }
                            else
                            {
                                muser.FailedPasswordAnswerAttemptCount = 0;
                                muser.FailedPasswordAnswerAttemptWindowStart = NullDate;
                                status = MembershipErrorCode.OK;
                            }

                            try
                            {
                                ctx.SaveChanges();


                            }
                            catch
                            {
                                status = MembershipErrorCode.ProviderError;
                                //TODO: log this?
                                return null;
                            }
                        }

                        if (status == MembershipErrorCode.OK)
                        {
                            password = userV.Password;
                            passwordFormat = userV.PasswordFormat;
                        }
                    }
                }
            }
            return password;
        }

        private void GetPasswordWithFormat(string username, bool updateLastLoginActivityDate,
                                                   out MembershipErrorCode status,
                                                   out string password,
                                                   out int passwordFormat, out string passwordSalt,
                                                   out int failedPasswordAttemptCount,
                                                   out int failedPasswordAnswerAttemptCount, out bool isApproved,
                                                   out DateTime lastLoginDate,
                                                   out DateTime lastActivityDate)
        {
            status = MembershipErrorCode.ProviderError; //-1= error, 1=null, 99=locked out
            password = null;
            passwordFormat = 0;
            passwordSalt = null;
            failedPasswordAttemptCount = 0;
            failedPasswordAnswerAttemptCount = 0;
            isApproved = false;
            lastLoginDate = DateTime.UtcNow;
            lastActivityDate = DateTime.UtcNow;


            try
            {
                using (SQLiteEntityConnectionHolder holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString))
                {
                    using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                    {
                        Application app = GetOrCreateApplication(ctx);
                        AspUserMergedView userV =
                            ctx.AspUsersMergedView.FirstOrDefault(
                                u => u.LoweredUserName == username.ToLower() && u.ApplicationId == app.ApplicationId);
                        if (userV != null)
                        {
                            if (userV.IsLockedOut)
                            {
                                status = MembershipErrorCode.MembershipAccountLockOut;
                            }
                            else
                            {
                                if (updateLastLoginActivityDate)
                                {
                                    AspUser user = ctx.AspUsers.First(u => u.UserId == userV.UserId);
                                    user.LastActivityDate = ProviderUtility.Now();
                                    AspMembershipUser muser = ctx.AspMembershipUsers.First(u => u.UserId == userV.UserId);
                                    muser.LastLoginDate = user.LastActivityDate.GetValueOrDefault();
                                    ctx.SaveChanges();
                                    userV.LastActivityDate = user.LastActivityDate;
                                    userV.LastLoginDate = muser.LastLoginDate;
                                }

                                password = userV.Password;
                                passwordFormat = userV.PasswordFormat;
                                passwordSalt = userV.PasswordSalt;
                                failedPasswordAttemptCount = userV.FailedPasswordAttemptCount;
                                failedPasswordAnswerAttemptCount = userV.FailedPasswordAnswerAttemptCount;
                                isApproved = userV.IsApproved;
                                lastLoginDate = userV.LastLoginDate;
                                lastActivityDate = userV.LastActivityDate.GetValueOrDefault();
                                status = 0;
                            }
                        }
                        else
                        {
                            status = MembershipErrorCode.MembershipUserNotFound;
                        }
                    }
                }
            }
            catch
            {
                status = MembershipErrorCode.ProviderError;
            }
        }

        private static bool IsStatusDueToBadPassword(MembershipErrorCode status)
        {
            switch (status)
            {
                case MembershipErrorCode.MembershipWrongPassword:
                case MembershipErrorCode.MembershipWrongAnswer:
                case MembershipErrorCode.MembershipInvalidPassword:
                case MembershipErrorCode.MembershipInvalidQuestion:
                case MembershipErrorCode.MembershipInvalidAnswer:
                case MembershipErrorCode.MembershipAccountLockOut:
                    return true;
                default:
                    return false;
            }
        }

        private string UnEncodePassword(string pass, int passwordFormat)
        {
            switch (passwordFormat)
            {
                case 0: // MembershipPasswordFormat.Clear:
                    return pass;
                case 1: // MembershipPasswordFormat.Hashed:
                    throw new ProviderException(SR.GetString(SR.ProviderCanNotDecodeHashedPassword));
                default:
                    byte[] bIn = Convert.FromBase64String(pass);
                    byte[] bRet = DecryptPassword(bIn);
                    if (bRet == null)
                        return null;
                    return Encoding.Unicode.GetString(bRet, 16, bRet.Length - 16);
            }
        }

        #endregion

        //2345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
    }
}